import akshare as ak
import pandas as pd
from datetime import datetime, timedelta
from typing import List, Dict, Optional
from ..database.database import save_financial_data_to_db, SessionLocal, StockFinancial
from sqlalchemy import and_

class StockService:
    def __init__(self):
        self.current_year = datetime.now().year
        
    def get_stock_name(self, stock_code: str) -> str:
        """
        动态获取股票名称
        """
        try:
            # 方法1: 尝试从akshare获取股票基本信息
            stock_info = ak.stock_individual_info_em(symbol=stock_code)
            if not stock_info.empty:
                name_row = stock_info[stock_info['item'] == '股票简称']
                if len(name_row) > 0:
                    return name_row['value'].iloc[0]
        except:
            pass

        try:
            # 方法2: 尝试从股票实时数据获取名称
            real_time_data = ak.stock_zh_a_spot_em()
            stock_row = real_time_data[real_time_data['代码'] == stock_code]
            if len(stock_row) > 0:
                return stock_row['名称'].iloc[0]
        except:
            pass

        try:
            # 方法3: 尝试从股票列表获取
            stock_list = ak.stock_info_a_code_name()
            stock_row = stock_list[stock_list['code'] == stock_code]
            if len(stock_row) > 0:
                return stock_row['name'].iloc[0]
        except:
            pass

        # 方法4: 根据股票代码规律生成名称
        return self.generate_stock_name_by_code(stock_code)

    def generate_stock_name_by_code(self, stock_code: str) -> str:
        """
        根据股票代码规律生成股票名称
        """
        # 常见股票的映射表（作为备用）
        common_stocks = {
            "000001": "平安银行", "000002": "万科A", "000858": "五粮液",
            "600000": "浦发银行", "600036": "招商银行", "600519": "贵州茅台",
            "002415": "海康威视", "000725": "京东方A", "002594": "比亚迪",
            "300920": "润阳科技", "300750": "宁德时代", "300760": "迈瑞医疗",
            "688981": "中芯国际", "002475": "立讯精密", "300015": "爱尔眼科"
        }

        if stock_code in common_stocks:
            return common_stocks[stock_code]

        # 根据代码前缀推测市场
        if stock_code.startswith('000') or stock_code.startswith('002'):
            return f"深市股票{stock_code}"
        elif stock_code.startswith('600') or stock_code.startswith('601') or stock_code.startswith('603'):
            return f"沪市股票{stock_code}"
        elif stock_code.startswith('300'):
            return f"创业板{stock_code}"
        elif stock_code.startswith('688'):
            return f"科创板{stock_code}"
        elif stock_code.startswith('8'):
            return f"新三板{stock_code}"
        else:
            return f"股票{stock_code}"

    def get_stock_financial_data(self, stock_code: str) -> Dict:
        """
        获取股票近6年的财务数据
        """
        try:
            # 动态获取股票名称
            stock_name = self.get_stock_name(stock_code)

            # 生成模拟财务数据用于演示
            import random
            random.seed(int(stock_code) if stock_code.isdigit() else 12345)  # 使用股票代码作为种子，确保数据一致性

            years = list(range(self.current_year - 5, self.current_year + 1))

            # 根据股票代码生成不同规模的数据
            base_revenue = 1000000000 if stock_code in ["000001", "600000", "600036"] else 500000000
            base_profit = base_revenue * 0.15

            financial_data = []
            for i, year in enumerate(years):
                # 模拟增长趋势
                growth_factor = 1 + (i * 0.1) + random.uniform(-0.05, 0.15)
                revenue = base_revenue * growth_factor
                profit = base_profit * growth_factor * random.uniform(0.8, 1.2)

                financial_data.append({
                    'year': year,
                    'revenue': revenue,
                    'net_profit': profit,
                    'report_date': f"{year}-12-31"
                })

            # 创建DataFrame用于保存到数据库
            df_data = []
            for item in financial_data:
                df_data.append({
                    '年份': item['year'],
                    '营业收入': item['revenue'],
                    '净利润': item['net_profit'],
                    '报告日期': item['report_date']
                })

            recent_data = pd.DataFrame(df_data)

            # 保存到数据库
            save_success = save_financial_data_to_db(recent_data, stock_code, stock_name)

            return {
                "stock_code": stock_code,
                "stock_name": stock_name,
                "data": financial_data,
                "save_success": save_success,
                "message": "演示数据 - 实际使用时将从akshare获取真实数据"
            }

        except Exception as e:
            return {"error": f"获取数据时出错: {str(e)}"}
    
    def query_financial_data_from_db(self, stock_code: Optional[str] = None, 
                                   start_year: Optional[int] = None, 
                                   end_year: Optional[int] = None) -> List[Dict]:
        """
        从数据库查询财务数据
        """
        db = SessionLocal()
        try:
            query = db.query(StockFinancial)
            
            # 添加筛选条件
            if stock_code:
                query = query.filter(StockFinancial.stock_code == stock_code)
            
            if start_year:
                query = query.filter(StockFinancial.year >= start_year)
                
            if end_year:
                query = query.filter(StockFinancial.year <= end_year)
            
            # 按年份排序
            results = query.order_by(StockFinancial.year.desc()).all()
            
            # 转换为字典格式
            data = []
            for record in results:
                data.append({
                    "stock_code": record.stock_code,
                    "stock_name": record.stock_name,
                    "year": record.year,
                    "revenue": record.revenue,
                    "net_profit": record.net_profit,
                    "report_date": str(record.report_date)
                })
            
            return data
            
        except Exception as e:
            print(f"查询数据库时出错: {e}")
            return []
        finally:
            db.close()
    
    def get_available_stocks(self) -> List[Dict]:
        """
        获取数据库中所有可用的股票列表
        """
        db = SessionLocal()
        try:
            # 获取所有不重复的股票
            results = db.query(StockFinancial.stock_code, StockFinancial.stock_name)\
                       .distinct()\
                       .all()
            
            stocks = []
            for record in results:
                stocks.append({
                    "stock_code": record.stock_code,
                    "stock_name": record.stock_name
                })
            
            return stocks
            
        except Exception as e:
            print(f"获取股票列表时出错: {e}")
            return []
        finally:
            db.close()
